package it.omnisong.server.persistence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import it.omnisong.client.model.Song;
import it.omnisong.server.persistence.postgres.DataBaseManager;
import it.omnisong.server.persistence.postgres.PostgresException;

public class SongDAO {

	private Connection connection;

	public SongDAO() throws PostgresException {
		try {
			connection = DataBaseManager.getConnection();
			
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}		
	}
	
	public int insertSong(Song song) throws PostgresException {
		
		int songId;
		try {
			
			songId = getNextId();
			PreparedStatement psti = connection.prepareStatement("INSERT INTO song (id, title, lyric, artist) VALUES (?, ?, ?, ?)");
			psti.setInt(1, songId);
			psti.setString(2, song.getTitle());
			psti.setString(3, song.getLyric());
			psti.setString(4, song.getArtist());
			psti.executeUpdate();
			psti.close();
		
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}

		return songId;
	}
	
	
	public int getNextId() throws PostgresException {
		

		int songId = -1;
		Statement stid;
		
		try {
			
			stid = connection.createStatement();
			ResultSet rsid = stid.executeQuery("SELECT nextval('song_id_seq')");
			rsid.next();
			songId = rsid.getInt(1);
			rsid.close();
			stid.close();
			
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}
		
		return songId;
	}
	
	public Song get(int id) throws PostgresException {
		Song song = null;
		PreparedStatement psts;
		try {
			
			psts = connection.prepareStatement("SELECT * from song where id=?");
			psts.setInt(1, id);
			ResultSet rs = psts.executeQuery();
			rs.next();
			
			song = new Song();
			song.setId(rs.getInt("id"));
			song.setTitle(rs.getString("title"));
			song.setLyric(rs.getString("lyric"));
			song.setArtist(rs.getString("artist"));
		
			rs.close();
			psts.close();
		
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}
		
		System.out.println("Selezionata Song: " + song);
		
		return song;
	}

	public List<Song> get(String title) throws PostgresException {
		
		List<Song> songs = new ArrayList<Song>();
		PreparedStatement psts;
		try {
			
			psts = connection.prepareStatement("SELECT * from song where title=?");
			psts.setString(1, title);
			ResultSet rs = psts.executeQuery();
			while (rs.next()) {
				
				Song song = new Song();
				song.setId(rs.getInt("id"));
				song.setTitle(rs.getString("title"));
				song.setLyric(rs.getString("lyric"));
				song.setArtist(rs.getString("artist"));
				
				songs.add(song);
				System.out.println("Selezionata Song: " + song);
			}
		
			rs.close();
			psts.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		
		return songs;
	}
	
	public List<Song> getAll() throws PostgresException {
		
		List<Song> songs = new ArrayList<Song>();
		PreparedStatement psts;
		try {
			
			psts = connection.prepareStatement("SELECT * from song");
			ResultSet rs = psts.executeQuery();
			while (rs.next()) {
				Song song = new Song();
				song.setId(rs.getInt("id"));
				song.setTitle(rs.getString("title"));
				song.setLyric(rs.getString("lyric"));
				song.setArtist(rs.getString("artist"));

				songs.add(song);
			}	
		
			rs.close();
			psts.close();	
		
		} catch (SQLException e) {
			throw new PostgresException(e.getMessage());
		}	
		
		return songs;
	}
	
	public List<Song> get(String title, String artist) throws PostgresException {
		
		List<Song> songs = new ArrayList<Song>();
		PreparedStatement psts;
		try {
		
			psts = connection.prepareStatement("SELECT * from song where title=? and artist=?");
			psts.setString(1, title);
			psts.setString(2, artist);
		
			ResultSet rs = psts.executeQuery();
			while (rs.next()) {
				Song song = new Song();
				song.setId(rs.getInt("id"));
				song.setTitle(rs.getString("title"));
				song.setLyric(rs.getString("lyric"));
				song.setArtist(rs.getString("artist"));

				songs.add(song);
				System.out.println("Selezionata Song: " + song);
			}
		
			rs.close();
			psts.close();	
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		
		return songs;
	}
	
}
